<?php

class ModulosaliasController extends GxController {

    private $page;
    private $limit;
    private $sidx;
    private $sord;
    private $wh;
    private $total_pages;
    private $count;
    private $start;

    public function filters() {
        return array(
            'accessControl',
        );
    }

    public function accessRules() {
        return array(
            array('allow',
                'actions' => array('index'),
                'users' => array('*'),
            ),
            array('allow',
                'actions' => array('minicreate', 'index', 'cargaNucleos', 'cargaralias', 'cargaGrilla', 'agregaAlias', 'editaAlias'),
                'users' => array('*'),
            ),
            array('allow',
                'actions' => array('admin', 'extraFacultad'),
                'users' => array('*'),
            ),
            array('deny',
                'users' => array('*'),
            ),
        );
    }

    public function actionIndex() {
        Controller::registrar_scrips_jquery_oly();
        Controller::registrar_scrips_jquery();
        Controller::registrar_scrips();
        Controller::registrar_scrips_jqgrid();
        $this->layout = '//layouts/column3';
        $this->render('index');
    }

    public function actionextraFacultad() {
        $connection = Yii::app()->db;
        $command = $connection->createCommand("select nombfacultad,codfacultad from egresados_dblinks where sede = '{$_POST["sede"]}' group by nombfacultad,codfacultad order by nombfacultad");
        $results = $command->queryAll();
        $campo = "<option value=''></option>";
        foreach ($results as $rows) {
            $valor = array_values($rows);
            $campo .="<option value='$valor[1]'>$valor[0]</option>";
        }
        echo $campo;
    }

//fin public function actionExtraFacultad() {

    public function actionagregaAlias() {
        $tipNucleo = trim($_POST["tipNucleo"]);
        $alias = trim($_POST["alias"]);
        $modulo = trim($_POST["modulo"]);
        $nucleo = trim($_POST["nucleo"]);
        $orden = trim($_POST["orden"]);
        if ($tipNucleo == "" || $alias == "" || $modulo == "" || $nucleo == "" || $orden == "" || !is_numeric($orden))
            exit("Parametros incorrectos");
        $campo = "";
        $tabla = "";
        switch ($tipNucleo) {
            case 'especifico':
                $campo = "NCLE_ID";
                $tabla = "NUCLEOSESPECIFICOSALIAS";
                break;
            case 'basico':
                $campo = "NCLB_ID";
                $tabla = "NUCLEOSBASICOSALIAS";
                break;
        }
        $connection = Yii::app()->db;
//        $order = $connection->createCommand("SELECT MAX(ORDEN) + 1 FROM $tabla WHERE $campo=$nucleo AND MOD_ID=$modulo")->queryScalar();
//        if (!is_numeric($order))
//            $order = 0;
        $sql = "SELECT count(ORDEN) as total FROM $tabla WHERE ALS_ID = $alias and MOD_ID = $modulo and  $campo=$nucleo";
        $existe = $connection->createCommand($sql)->queryScalar();
        if ($existe > 0) {
            echo 2;
            return;
        }
        $sql = "SELECT count(ORDEN) as total FROM $tabla WHERE ORDEN = $orden and MOD_ID = $modulo and  $campo=$nucleo";
        $existe = $connection->createCommand($sql)->queryScalar();
        if ($existe > 0) {
            echo 3;
            return;
        }
        $qry = "INSERT INTO $tabla ( ALS_ID, $campo, MOD_ID, ORDEN ) VALUES ($alias, $nucleo, $modulo, $orden )";
        try {
            $command = $connection->createCommand($qry);
            $rowCount = $command->execute();
        } catch (Exception $e) {  //an exception is raised if a query fails
            echo $e;
        }
        echo 1;
        return;
    }

//fin public function actionagregaAlias() {

    public function actioneditaAlias() {
        $tipNucleo = trim($_POST["tipNucleo"]);
        $alias = trim($_POST["id"]);
        $modulo = trim($_POST["modulo"]);
        $nucleo = trim($_POST["nucleo"]);
        $oper = trim($_POST["oper"]);
        if ($tipNucleo == "" || $alias == "" || $modulo == "" || $nucleo == "" || $oper == "")
            exit("Parametros incorrectos");
        $campo = "";
        $tabla = "";
        switch ($tipNucleo) {
            case 'especifico':
                $campo = "NCLE_ID";
                $tabla = "NUCLEOSESPECIFICOSALIAS";
                break;
            case 'basico':
                $campo = "NCLB_ID";
                $tabla = "NUCLEOSBASICOSALIAS";
                break;
        }
        $connection = Yii::app()->db;
        switch ($oper) {
            case 'edit':
                $orden = trim($_POST["ORDEN"]);
                if (!is_numeric($orden))
                    exit("Parametros incorrectos");
                $sql = "SELECT count(ORDEN) as total FROM $tabla WHERE ORDEN = $orden and MOD_ID = $modulo and  $campo=$nucleo";
                $existe = $connection->createCommand($sql)->queryScalar();
                if ($existe > 0) {
                    echo 3;
                    return;
                }
                $sql = "update $tabla set ORDEN = $orden where ALS_ID = $alias and $campo = $nucleo and MOD_ID = $modulo";
                break;
            case 'del':
                $sql = "delete from $tabla where ALS_ID = $alias and $campo = $nucleo and MOD_ID = $modulo";
                break;
            default:
                exit("Parametros error");
                break;
        }
        try {
            $command = $connection->createCommand($sql);
            $command->execute();
        } catch (Exception $e) {  //an exception is raised if a query fails
            echo $e;
        }
        echo 1;
        return;
    }

//fin public function actioneditaAlias() {

    public function actioncargaNucleos($opcion) {
        if (trim($opcion) == "")
            exit("Errr. Parametros incorrectos");
        $tabla = "";
        $campos = "";
        $order = "";
        switch ($opcion) {
            case 'especifico':
                $campos = "NCLEID, NCLENOMBRE";
                $tabla = "NUCLEOSESPECIFICOS";
                $order = "NCLENOMBRE";
                break;
            case 'basico':
                $campos = "NCLBID, NCLBNOMBRE";
                $tabla = "NUCLEOSBASICOS";
                $order = "NCLBNOMBRE";
                break;
        }
        $connection = Yii::app()->db;
        $command = $connection->createCommand("select $campos FROM $tabla ORDER BY $order");
        $results = $command->queryAll();
        $campo = "<option value=''></option>";
        foreach ($results as $rows) {
            $valor = array_values($rows);
            $campo .="<option value='$valor[0]'>$valor[1]</option>";
        }
        echo $campo;
    }

//fin public function actioncargaNucleos($opcion) {
    public function actioncargaralias($nucleo, $tipNucleo) {
        if (trim($nucleo) == "" || trim($tipNucleo) == "")
            exit("Errr. Parametros incorrectos");
        echo "hola";
        $tabla = "";
        $campos = "";
        switch ($tipNucleo) {
            case 'especifico':
                $campos = "NCLE_ID";
                $tabla = "NUCLEOSESPECIFICOSALIAS";
                break;
            case 'basico':
                $campos = "NCLB_ID";
                $tabla = "NUCLEOSBASICOSALIAS";
                break;
        }
        $sql = "SELECT ALSID,ALSNOMBRE FROM ALIAS
                WHERE ALSID NOT IN
                (SELECT ALS_ID FROM $tabla WHERE $campos=$nucleo)
                ORDER BY ALSNOMBRE";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $results = $command->queryAll();
        $campo = "<option value=''>Seleccione...</option>";
        foreach ($results as $rows) {
            $valor = array_values($rows);
            $campo .="<option value='$valor[0]'>$valor[1]</option>";
        }
        echo $campo;
    }

//fin public function cargaralias($opcion) {

    public function actioncargaGrilla($nucleo, $modulo, $tipoNucleo) {
        if (trim($nucleo) == "" || trim($modulo) == "" || trim($tipoNucleo) == "")
            exit("Parametros invalidos");
        $tabla = "";
        $campo = "";
        switch ($tipoNucleo) {
            case 'especifico':
                $campo = "NCLE_ID";
                $tabla = "NUCLEOSESPECIFICOSALIAS";
                break;
            case 'basico':
                $campo = "NCLB_ID";
                $tabla = "NUCLEOSBASICOSALIAS";
                break;
        }
        $connection = Yii::app()->db;
        $this->cargaParametrosGrillaIniciales();
        $innerJoin = " inner join  $tabla b ON a.ALSID    =b.ALS_ID ";
        $where = " WHERE b.MOD_ID=$modulo AND b.$campo = $nucleo " . $this->wh;
        $sql = "SELECT count(*) as total from ALIAS a " . $innerJoin . $where;
        $command = $connection->createCommand($sql);
        $results = $command->queryAll();
        foreach ($results as $rows) {
            $valor = array_values($rows);
            $this->totalesGrilla($valor[0]);
        }
        $sql = "SELECT ALSID,ALSNOMBRE,b.ORDEN FROM ALIAS a " . $innerJoin . $where . " and rownum >= $this->start and rownum <= $this->limit" . " ORDER BY " . $this->sidx . " " . $this->sord;
        $command = $connection->createCommand($sql);
        $results = $command->queryAll();
        $responce->page = $this->page;
        $responce->total = $this->total_pages;
        $responce->records = $this->count;
        $i = 0;
        foreach ($results as $value) {
            $value = array_values($value);
            $responce->rows[$i]['id'] = $value[0];
            $responce->rows[$i]['cell'] = array($value[1], $value[2]);
            $i++;
        }
        echo json_encode($responce);
    }

    private function Strip($value) {
        if (get_magic_quotes_gpc() != 0) {
            if (is_array($value))
                if ($this->array_is_associative($value)) {
                    foreach ($value as $k => $v)
                        $tmp_val[$k] = stripslashes($v);
                    $value = $tmp_val;
                }
                else
                    for ($j = 0; $j < sizeof($value); $j++)
                        $value[$j] = stripslashes($value[$j]);
            else
                $value = stripslashes($value);
        }
        return $value;
    }

    private function getStringForGroup($group) {
        $i_ = '';
        $sopt = array('eq' => "=", 'ne' => "<>", 'lt' => "<", 'le' => "<=", 'gt' => ">", 'ge' => ">=", 'bw' => " {$i_}LIKE ", 'bn' => " NOT {$i_}LIKE ", 'in' => ' IN ', 'ni' => ' NOT IN', 'ew' => " {$i_}LIKE ", 'en' => " NOT {$i_}LIKE ", 'cn' => " {$i_}LIKE ", 'nc' => " NOT {$i_}LIKE ", 'nu' => 'IS NULL', 'nn' => 'IS NOT NULL');
        $s = "(";
        if (isset($group['groups']) && is_array($group['groups']) && count($group['groups']) > 0) {
            for ($j = 0; $j < count($group['groups']); $j++) {
                if (strlen($s) > 1) {
                    $s .= " " . $group['groupOp'] . " ";
                }
                try {
                    $dat = $this->getStringForGroup($group['groups'][$j]);
                    $s .= $dat;
                } catch (Exception $e) {
                    echo $e->getMessage();
                }
            }
        }
        if (isset($group['rules']) && count($group['rules']) > 0) {
            try {
                foreach ($group['rules'] as $key => $val) {
                    if (strlen($s) > 1) {
                        $s .= " " . $group['groupOp'] . " ";
                    }
                    $field = $val['field'];
                    $op = $val['op'];
                    $v = $val['data'];
                    if ($op) {
                        switch ($op) {
                            case 'bw':
                            case 'bn':
                                $s .= $field . ' ' . $sopt[$op] . "'$v%'";
                                break;
                            case 'ew':
                            case 'en':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v'";
                                break;
                            case 'cn':
                            case 'nc':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v%'";
                                break;
                            case 'in':
                            case 'ni':
                                $s .= $field . ' ' . $sopt[$op] . "( '$v' )";
                                break;
                            case 'nu':
                            case 'nn':
                                $s .= $field . ' ' . $sopt[$op] . " ";
                                break;
                            default :
                                $s .= $field . ' ' . $sopt[$op] . " '$v' ";
                                break;
                        }
                    }
                }
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        }
        $s .= ")";
        if ($s == "()") {
            //return array("",$prm); // ignore groups that don't have rules
            return " 1=1 ";
        } else {
            return $s;
            ;
        }
    }

//fin private function getStringForGroup($group){

    private function array_is_associative($array) {
        if (is_array($array) && !empty($array)) {
            for ($iterator = count($array) - 1; $iterator; $iterator--) {
                if (!array_key_exists($iterator, $array)) {
                    return true;
                }
            }
            return !array_key_exists(0, $array);
        }
        return false;
    }

    private function cargaParametrosGrillaIniciales() {
        $this->page = $_POST['page'];
        $this->limit = $_POST['rows'];
        $this->sidx = ($_POST['sidx']) ? $_POST['sidx'] : 1;
        $this->sord = $_POST['sord'];
        //Parametros de búsqueda
        $this->wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $this->wh = " AND " . $this->getStringForGroup($jsona);
        }
    }

//fin private function cargaParametrosGrillaIniciales() {
    private function totalesGrilla($count) {
        $this->count = $count;
        if ($count > 0)
            $this->total_pages = ceil($count / $this->limit);
        else
            $this->total_pages = 0;
        if ($this->page > $this->total_pages)
            $this->page = $this->total_pages;
        $this->start = $this->limit * $this->page - $this->limit; // do not put $limit*($page - 1)
        $this->start = ($this->start > 0) ? $this->start : 0;
    }

}